********************************************************************************
*                                                                              *
*                     Final Dataset PS Descriptive Statistics                  *
*                                                                              *
********************************************************************************

cd "H:\"

global finalyear = 2020
global fevarcompete beid sbi4#year mun#year c.logvalueaddedpworker09#year c.logworkers09#year


////////////////////////////////////////////////////////////////////////////////
/////////////////Imports and cumulative imports of robots by year///////////////
////////////////////////////////////////////////////////////////////////////////

* Create Figure 1

preserve 
	putexcel set "Robots and Workers\Results\Figures.xlsx", modify sheet(Descriptives)
	putexcel A2 = "Year"
	putexcel B2 = "Robot imports"
	putexcel C2 = "Cumulative robot imports"
	putexcel D2 = "Robot importing firms" 
	tabstat imp_robots cimp_robots robots, by(year) stat(sum) save
	forvalues i = 1(1)12 {
		local year = `i'+2008
		local row = `i'+3
		matrix A = [`year', r(Stat`i')]
		putexcel A`row' = matrix(A)
	}
restore

////////////////////////////////////////////////////////////////////////////////
//////////////////////////Value added by robots firms///////////////////////////
////////////////////////////////////////////////////////////////////////////////

* Create Figure 2

preserve
	keep if year == $finalyear
	gsort -valueadded
	g pctile = int((_n/_N)*20)*5
	collapse (sum) robots cimp_robots, by(pctile)
	total robots
	replace robots = robots/_b[robots]
	total cimp_robots
	replace cimp_robots = cimp_robots/_b[cimp_robots]
	label variable pctile "Percentile of value added"
	label variable robots "Share of robot adopters"
	label variable cimp_robots "Share of robot value"
	export excel using "Robots and Workers\Results\Figures.xlsx", sheet("Descriptives") sheetmodify cell(A50) firstrow(varlabels)
restore


////////////////////////////////////////////////////////////////////////////////
///////////////////////////////Create histograms////////////////////////////////
////////////////////////////////////////////////////////////////////////////////

* Create Figure A2 

putexcel set "Robots and Workers\Results\Figures.xlsx", modify sheet(Histograms)
serset drop _all

preserve 
	histogram logvalueadded, start(0) width(0.25)
	graph save "Graph" "Robots and Workers\Results\Graph.gph", replace
	clear
	graph use "Robots and Workers\Results\Graph.gph"
	serset dir
	serset use
	export excel using "Robots and Workers\Results\Figures.xlsx", sheet("Histograms") sheetmodify cell(A25) firstrow(variables)
	putexcel A25 = "Log of value added"
	putexcel B25 = ""
	putexcel C25 = ""
restore

preserve 
	drop if loghoursworked < 0
	histogram loghoursworked, start(0) width(0.25)
	graph save "Graph" "Robots and Workers\Results\Graph.gph", replace
	clear
	graph use "Robots and Workers\Results\Graph.gph"
	serset dir
	serset use
	export excel using "Robots and Workers\Results\Figures.xlsx", sheet("Histograms") sheetmodify cell(E25) firstrow(variables)
	putexcel E25 = "Log of hoursworked"
	putexcel F25 = ""
	putexcel G25 = ""
restore

preserve 
	histogram loghwage, start(0) width(0.15)
	graph save "Graph" "Robots and Workers\Results\Graph.gph", replace
	clear
	graph use "Robots and Workers\Results\Graph.gph"
	serset dir
	serset use
	export excel using "Robots and Workers\Results\Figures.xlsx", sheet("Histograms") sheetmodify cell(I25) firstrow(variables)
	putexcel I25 = "Log of hourly wage"
	putexcel J25 = ""
	putexcel K25 = ""
restore

preserve 
	histogram laborshare, start(0) width(0.015)
	graph save "Graph" "Robots and Workers\Results\Graph.gph", replace
	clear
	graph use "Robots and Workers\Results\Graph.gph"
	serset dir
	serset use
	export excel using "Robots and Workers\Results\Figures.xlsx", sheet("Histograms") sheetmodify cell(M25) firstrow(variables)
	putexcel M25 = "Labor share"
	putexcel N25 = ""
	putexcel O25 = ""
restore
capture erase "Robots and Workers\Results\Graph.gph"

////////////////////////////////////////////////////////////////////////////////
///////////////////////////Create descriptive tables////////////////////////////
////////////////////////////////////////////////////////////////////////////////


* Create Table A2

preserve
	keep valueadded hoursworked workers laborshare wagebill hwage salesphour capitalratio robots imp_robots compete firmage shmeffect shreplaceable
	order valueadded hoursworked workers laborshare wagebill hwage salesphour capitalratio robots imp_robots compete  firmage shmeffect shreplaceable
	global N = _N
	outreg2 using "Robots and Workers\Results\Descriptives_PS", replace sum(detail) eqkeep(mean sd p5 p50 p95 N) label word tex addnote(The number of observations is $N.)
restore	
preserve
	keep if robotsfirm==0
	keep valueadded hoursworked workers laborshare wagebill hwage salesphour capitalratio robots imp_robots compete  firmage shmeffect shreplaceable
	order valueadded hoursworked workers laborshare wagebill hwage salesphour capitalratio robots imp_robots compete  firmage shmeffect shreplaceable
	global N = _N
	outreg2 using "Robots and Workers\Results\Descriptives_PS_norobots", replace sum(detail) eqkeep(mean sd p5 p50 p95 N) label word tex addnote(The number of observations is $N.)
restore	
preserve
	keep if robotsfirm==1
	keep valueadded hoursworked workers laborshare wagebill hwage salesphour capitalratio robots imp_robots compete  firmage shmeffect shreplaceable
	order valueadded hoursworked workers laborshare wagebill hwage salesphour capitalratio robots imp_robots compete  firmage shmeffect shreplaceable
	global N = _N
	outreg2 using "Robots and Workers\Results\Descriptives_PS_robots", replace sum(detail) eqkeep(mean sd p5 p50 p95 N) label word tex addnote(The number of observations is $N.)
restore	


* Create Table A3
use "Robots and Workers\Data\BEID_PS.dta", clear
	keep if manufacturing==1
	keep if year == 2009 | year == 2020

foreach var in robots logvalueadded logvalueaddedphour laborshare loghwage loghoursworked logsalesphour capitalratio compete {
	g Δ`var' = `var'[_n+1]-`var' if beid == beid[_n+1]

}
label variable Δrobots "Robot adopter"
label variable Δcompete "Share of sales in robot adopting firms"
label variable logworkers "Number of workers in t-6 \textit{(log)}"
label variable logvalueaddedpworker "Value added per worker in t-6 \textit{(log)}"

preserve
	keep if hoursworked09 !=.
	duplicates tag beid, g(tag)
	drop if tag == 0
	drop tag
	keep valueadded hoursworked workers laborshare wagebill hwage salesphour capitalratio robots imp_robots robotsfirm compete
 	order valueadded hoursworked workers laborshare wagebill hwage salesphour capitalratio robots imp_robots robotsfirm compete
	save "Robots and Workers\Data\BEID_PS_temp.dta", replace
	
	clear all 
	use "Robots and Workers\Data\BEID_PS_temp.dta", clear
	global N = _N
	outreg2 using "Robots and Workers\Results\Descriptives_PS_FD", replace sum(detail) eqkeep(mean sd p5 p50 p95 N) label word tex addnote(The number of observations is $N.)
	
	use "Robots and Workers\Data\BEID_PS_temp.dta", clear
	keep if robotsfirm == 0	 
	global N = _N
	outreg2 using "Robots and Workers\Results\Descriptives_PS_FD_norobots", replace sum(detail) eqkeep(mean sd p5 p50 p95 N) label word tex addnote(The number of observations is $N.)

	use "Robots and Workers\Data\BEID_PS_temp.dta", clear
	keep if robotsfirm == 1
	global N = _N
	outreg2 using "Robots and Workers\Results\Descriptives_PS_FD_robots", replace sum(detail) eqkeep(mean sd p5 p50 p95 N) label word tex addnote(The number of observations is $N.)
	
	erase "Robots and Workers\Data\BEID_PS_temp.dta"
restore	


////////////////////////////////////////////////////////////////////////////////
///////////////////////////Robot adopters by sector/////////////////////////////
////////////////////////////////////////////////////////////////////////////////

* Figure not in the paper 

preserve 
	tabstat robots, by(sector) stat(N) save, if year == $finalyear
	putexcel A22 = "Sector"
	putexcel B22 = "N"
	putexcel C22 = "Share robot adopters"
	local i = 1

	foreach sector in "Construction" "Mining" "Energy" "Manufacturing" "Transport and logistics" "Water and waste" {
		local row = `i'+22
		matrix A = r(Stat`i')
		putexcel A`row' = "`sector'"
		putexcel B`row' = matrix(A)
		local i = `i'+1
	}
	local i = 1
	tabstat robots, by(sector) stat(mean) save, if year == $finalyear

	foreach sector in "Construction" "Mining" "Energy" "Manufacturing" "Transport and logistics" "Water and waste" {
		local row = `i'+22
		matrix A = r(Stat`i')
		putexcel C`row' = matrix(A)
		local i = `i'+1
	}
restore 